/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.ban.ycl.ybk.e2p.yb.tools;

import cn.ac.iie.di.ban.ycl.ybk.e2p.yb.main.BanBkE2p;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 *
 * @author wmr
 */
public class JdbcUtil {

    private static org.apache.log4j.Logger logger = org.apache.log4j.Logger.getLogger(JdbcUtil.class);
//    static String filePath = JdbcUtil.class.getResource("/").getFile() + "oracle_jdbc.properties";
    static String filePath = BanBkE2p.confDir + "/oracle_jdbc.properties";

    private static String driver = "";
    private static String url = "";
    private static String userName = "";
    private static String password = "";

    static {
        InputStream in = null;
        try {
            Properties prop = new Properties();
//            InputStream in = new FileInputStream("oracle_jdbc.properties");
//            String filePath = "/home/wmr/NetBeansProjects/Maven_project/ban-bk-e2p-ysyb/src/main/resources" + "/oracle_jdbc.properties";
            in = new FileInputStream(filePath);
            prop.load(in);
            in.close();
            driver = prop.getProperty("driver").trim();
            url = prop.getProperty("url").trim();
            userName = prop.getProperty("UserName").trim();
            password = prop.getProperty("Password").trim();
        } catch (Exception ex) {
            logger.error("get jdbc error ---------- " + ex.getMessage(), ex);

        }
    }

    public Connection getConnection(Connection conn) {
        try {
//            Properties prop = new Properties();
////            InputStream in = new FileInputStream("oracle_jdbc.properties");
////            String filePath = "/home/wmr/NetBeansProjects/Maven_project/ban-bk-e2p-ysyb/src/main/resources" + "/oracle_jdbc.properties";
//            InputStream in = new FileInputStream(filePath);
//            prop.load(in);
//            in.close();
//            String driver = prop.getProperty("driver").trim();
//            String url = prop.getProperty("url").trim();
//            String userName = prop.getProperty("UserName").trim();
//            String password = prop.getProperty("Password").trim();
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
//            conn.close();
        } catch (Exception e) {
            logger.error("Database connection failed " + e.getMessage(), e);
        }
        return conn; //返回所建立的数据库连接     
    }

    public static boolean insert(Connection conn, String sql, Statement st) {
        boolean flag = true;
        try {
//            Statement st = conn.createStatement();    // 创建用于执行静态sql语句的Statement对象     
            int count = st.executeUpdate(sql);  // 执行插入操作的sql语句，并返回插入数据的个数     
            logger.info(" Insert into JSON table " + count + " row data"); //输出插入操作的处理结果
//            st.close();
        } catch (SQLException e) {
            flag = false;
            logger.error(" Failed to insert data" + e.getMessage(), e);
            return flag;
        }
        return flag;
    }

    public static List<String> selFile(Connection conn, String sql,Statement st) {
        boolean flag = true;
        int count = 0;
        List<String> cs = new ArrayList<String>();
        
        try {
//            Statement st = conn.createStatement();    // 创建用于执行静态sql语句的Statement对象     
            ResultSet res = st.executeQuery(sql);
            while (res.next()) {
                String code = res.getString(1);
                cs.add(code);
                count++;
            }
            logger.info(" select " + count + " row data ---------"); //输出查询操作的处理结果
            res.close();
//            st.close();
        } catch (SQLException e) {
            flag = false;
            logger.error(" Failed to select data" + e.getMessage(), e);
            cs.add("errorCODE");
            return cs;
        }
        return cs;
    }

    public static boolean delFile(Connection conn, String sql) {
        boolean flag = true;
        int count = 0;
        List<String> cs = new ArrayList<String>();
        
        try {
            Statement st = conn.createStatement();    // 创建用于执行静态sql语句的Statement对象     
            count = st.executeUpdate(sql);
            logger.info(" del " + count + " row data ---------"); //输出查询操作的处理结果
            st.close();
        } catch (SQLException e) {
            flag = false;
            logger.error(" Failed to select data" + e.getMessage(), e);
            cs.add("errorCODE");
            return flag;
        }
        return flag;
    }
    
//    public static void main(String[] args) throws SQLException {
//        JdbcUtil jdbcUtil = new JdbcUtil();
//        Connection conn = null;
//        conn = jdbcUtil.getConnection(conn);
//
//        int c = JdbcUtil.selFile(conn, "select count(*) from GXB_SUSPICIOUS_INFO where FILEID = 'sNEqdeIgiGw_' and SOURCECODE = 'MIAOPAI' and INSERTTIME >= to_char(sysdate-3,'yyyymmdd')");
//        conn.close();
//        conn=null;
//        
//        logger.info("---------------" + c);
////        List<String> jsonFileList = new ArrayList<String>();
////        System.out.println("size --- " + jsonFileList.size());
////        String[] s1 = {"1.json.ok", "1.json"};
////        for (int i = 0; i < s1.length; i++) {
////            if (s1[i].endsWith(".json.ok")) {
////                System.out.println(s1[i]);
////            }
////        }
//    }
}
